package mk.com.ldm.corepoint.implementation.server;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigInteger;
import java.nio.MappedByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;

import mk.com.ldm.corepoint.implementation.client.CoreService;
import mk.com.ldm.corepoint.implementation.shared.DataBaseObject;
import mk.com.ldm.corepoint.implementation.shared.EventObject;
import mk.com.ldm.corepoint.implementation.shared.Filter;
import mk.com.ldm.corepoint.implementation.shared.Foreign;
import mk.com.ldm.corepoint.implementation.shared.FormObject;
import mk.com.ldm.corepoint.implementation.shared.FormRow;
import mk.com.ldm.corepoint.implementation.shared.ListObject;
import mk.com.ldm.corepoint.implementation.shared.ListRow;
import mk.com.ldm.corepoint.implementation.shared.MenuObject;
import mk.com.ldm.corepoint.implementation.shared.TableColumn;
import mk.com.ldm.corepoint.implementation.shared.TableData;
import mk.com.ldm.corepoint.implementation.shared.TableRow;
import mk.com.ldm.corepoint.implementation.shared.TableRows;
import mk.com.ldm.corepoint.implementation.shared.WorkFlowObject;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

/**
 * 
 * ��������� ������������� �� CoreService, ��������� �� ������� �� �����������
 * ������ ������� �� ��������.
 * 
 */

@SuppressWarnings("serial")
public class CoreServiceImpl extends RemoteServiceServlet implements
		CoreService {

	public CoreServiceImpl() {
		DB.getInstance();
		CronScheduler.getInstance().run();
	}

	/**
	 * ������� �� �������� �� ��������. ������ �� ���� �� cookies � md5
	 * ���������.
	 */
	public String login(String username, String password) {
		System.out.println("user: "+username+"pass: "+password);
		
		String result = "";
		String hashPass = new String();
		ResultSet rs = null;

		MessageDigest m;
		try {
			m = MessageDigest.getInstance("MD5");
			m.reset();
			m.update(password.getBytes());
			byte[] digest = m.digest();
			BigInteger bigInt = new BigInteger(1, digest);
			hashPass = bigInt.toString(16);
			System.out.println("hash: "+hashPass);
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		}

		String sql = "SELECT * FROM users WHERE u_user = '" + username
				+ "' AND u_pass = '" + hashPass + "' LIMIT 1";
		
		System.out.println("sql: "+sql);

		rs = DB.execSql(sql);

		try {

			while (rs.next()) {
				m = MessageDigest.getInstance("MD5");
				result = rs.getString("u_user") + rs.getString("u_pass");
				m.reset();
				m.update(result.getBytes());
				byte[] digest = m.digest();
				BigInteger bigInt = new BigInteger(1, digest);
				result = bigInt.toString(16);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		return result;
	}

	/**
	 * ������� �� ������������ �� ������� ��������. ��������� ���� ����������
	 * ������ �� ���� � ���� ������������ md5 hash � ��� �� �� ��� � ������� ��
	 * cookie.
	 */
	public Boolean loginCheck(String username, String hash) {

		ResultSet rs = null;
		MessageDigest m;
		String result = new String();
		String sql = "SELECT * FROM users WHERE u_user = '" + username
				+ "' LIMIT 1";

		try {
			rs = DB.execSql(sql);
			while (rs.next()) {
				result = rs.getString("u_user") + rs.getString("u_pass");
			}
			m = MessageDigest.getInstance("MD5");
			m.reset();
			m.update(result.getBytes());
			byte[] digest = m.digest();
			BigInteger bigInt = new BigInteger(1, digest);
			result = bigInt.toString(16);

		} catch (Exception e) {
			e.printStackTrace();
		}

		if (hash.equals(result)) {
			return true;
		} else {
			return false;
		}
	}

	public String getJson(String username, String objectName, String type,
			String name) {
		username = "";
		if (objectName == null || objectName.isEmpty()) {
			objectName = "main";
		} else {
			objectName = objectName.trim();
		}

		String path = "../war/WEB-INF/config/";
		if (type.equals("User") || type.equals("Permission"))
			path += "Users/" + username + "/";
		else if (type.equals("Form") || type.equals("List")
				|| type.equals("Event") || type.equals("WorkFlow"))
			path += "Objects/" + objectName + "/";
		else
			path += type + "s/";

		File f = new File(path + username + name + type + ".map");
		if (f.exists()) {
			path += username + name + type + ".map";
		} else {
			path += username + type + ".map";
		}

		String json = null;
		try {
			FileInputStream fs = new FileInputStream(new File(path));
			FileChannel fc = fs.getChannel();
			MappedByteBuffer bb = fc.map(FileChannel.MapMode.READ_ONLY, 0,
					fc.size());
			json = Charset.defaultCharset().decode(bb).toString();
			fc.close();
			fs.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
//		
//		ReportGenerator.getInstance().generateReport();
//		
		return json;
	}

	public LinkedList<MenuObject> parseMenuMap() {

		String json = new String();
		try {
			FileInputStream fs = new FileInputStream(new File(
					"../war/WEB-INF/config/config.map"));
			FileChannel fc = fs.getChannel();
			MappedByteBuffer bb = fc.map(FileChannel.MapMode.READ_ONLY, 0,
					fc.size());
			json = Charset.defaultCharset().decode(bb).toString();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		Gson gson = new Gson();

		return gson.fromJson(json, new TypeToken<LinkedList<MenuObject>>() {
		}.getType());
	}

	public LinkedList<EventObject> getEvents(String objectName) {
		String json = getJson(objectName, "", "Event", "");
		Gson gson = new Gson();
		return gson.fromJson(json, new TypeToken<LinkedList<EventObject>>() {
		}.getType());
	}

	public FormObject parseConfigObject(String username, String objectName,
			String type, String name) {
		String json = getJson(username, objectName, type, name);
		Gson gson = new Gson();
		return gson.fromJson(json, FormObject.class);
	}

	public FormObject fetchConfigObject(String objectName, String itemId,
			String type, String name) {
		String json = getJson("", objectName, type, name);
		Gson gson = new Gson();
		FormObject form = gson.fromJson(json, FormObject.class);
		ArrayList<DataBaseObject> dbos = new ArrayList<DataBaseObject>();

		for (FormRow formRow : form.getConfRows()) {

			if (!formRow.getDbn().isEmpty()) {
				DataBaseObject dbo = new DataBaseObject();
				dbo.setDbName(formRow.getDbn());
				if (formRow.getDbn().equals(form.getPrimary())) {
					dbo.setConditions(true);
					dbo.addConditionValue(itemId);
					dbo.setFilter(false);
				}
				dbos.add(dbo);
			}
		}

		ArrayList<ArrayList<DataBaseObject>> data = new ArrayList<ArrayList<DataBaseObject>>();
		data.add(dbos);

		String query = queryBuilder("select", form.getTablename(), data);
		System.out.println(query);
		ResultSet rs = DB.execSql(query);
		try {
			while (rs.next()) {
				for (FormRow formRow : form.getConfRows()) {
					if (!formRow.getDbn().isEmpty()){
						String value = rs.getString(formRow.getDbn());
						if(formRow.getInp().equals("LV")){
							if(!formRow.getFgn().isEmpty()&&!value.isEmpty()){
								String table, alias, id, fName;
								String[] fkInfo = formRow.getFgn().split("/");
								table = fkInfo[0];
								alias = fkInfo[1];
								id = fkInfo[2];
								fName = fkInfo[3];
								DataBaseObject idDbo = new DataBaseObject(id, true);
								idDbo.setConditions(true);
								idDbo.setFilter(false);
								idDbo.addConditionValue(value);
								DataBaseObject nameDbo = new DataBaseObject(fName, true);
								ArrayList<DataBaseObject> fDbos = new ArrayList<DataBaseObject>();
								fDbos.add(idDbo);
								fDbos.add(nameDbo);
								ArrayList<ArrayList<DataBaseObject>> fData = new ArrayList<ArrayList<DataBaseObject>>();
								fData.add(fDbos);
								String sql = queryBuilder("select", table, fData);
								ResultSet fRs = DB.execSql(sql);
								fRs.next();
								formRow.setVal(fRs.getString(fName)+":/#"+fRs.getString(id));
							}
							else{
								formRow.setVal(value.isEmpty()?"":(value+":/#"+value));
							}
						}
						else
							formRow.setVal(value);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// System.out.println("ID: "+itemId);

		return form;
	}

	public ArrayList<String> saveObject(FormObject form, String itemId, String type, HashMap<String, String> params) {
		String retId = "", retName = "";
		ArrayList<String> ret = new ArrayList<String>();
		ArrayList<DataBaseObject> returnDbos = new ArrayList<DataBaseObject>();
		
		if(params!=null&&!params.isEmpty()){
			String json = getJson(params.get("username"), params.get("objectName"), params.get("type"), params.get("name"));
			Gson gson = new Gson();
			ListObject lo = gson.fromJson(json, ListObject.class);
			LinkedList<ListRow> lrs = lo.getRows();
			for(ListRow lr:lrs){
				if(lr.getHdr().equals(params.get("listViewReturn"))&&!lr.getDbn().isEmpty()&&!lr.getDbn().contains("SQL:")){
					DataBaseObject dbo = new DataBaseObject(lr.getDbn(), true);
					returnDbos.add(dbo);
				}
			}
		}

		ArrayList<DataBaseObject> dbos = new ArrayList<DataBaseObject>();
		for (FormRow formRow : form.getConfRows()) {
			if (!formRow.getDbn().isEmpty()) {
				DataBaseObject dbo = new DataBaseObject();
				dbo.setDbName(formRow.getDbn());
				dbo.setValue(formRow.getVal());
				if (form.getPrimary().equals(formRow.getDbn())
						&& !itemId.isEmpty()) {
					dbo.setConditions(true);
					dbo.addConditionValue(itemId);
					dbo.setFilter(false);
				}
				if(form.getPrimary().equals(formRow.getDbn()))
					retId = formRow.getVal();
				dbo.setNeedsSingleQuotes(true);
				dbos.add(dbo);
			}

		}

		ArrayList<ArrayList<DataBaseObject>> data = new ArrayList<ArrayList<DataBaseObject>>();
		data.add(dbos);

		String query = new String();

		if (type.equals("insert")) {
			query = queryBuilder("insert", form.getTablename(), data);
		} else {
			query = queryBuilder("update", form.getTablename(), data);
		}

//		System.out.println(query);
		Statement st = DB.insertSql(query);
		if (st != null&&type.equals("insert")) {
			try {
				if(st.getGeneratedKeys()!=null){
					ResultSet keys = st.getGeneratedKeys();
					if(keys.findColumn(form.getPrimary())!=0){
						keys.next();
						retId = keys.getString(form.getPrimary());
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DataBaseObject dbo = new DataBaseObject(form.getPrimary(), true);
			dbo.setConditions(true);
			dbo.setFilter(false);
			dbo.addConditionValue(retId);
			returnDbos.add(dbo);
			data = new ArrayList<ArrayList<DataBaseObject>>();
			data.add(returnDbos);
			ResultSet rs = DB.execSql(queryBuilder("select", form.getTablename(), data));
			if(rs!=null){
				try {
					rs.next();
					ret.add(retId);
					ret.add(rs.getString(returnDbos.get(0).getDbName()));
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return ret;

	}

	public Boolean deleteObject(String type, ArrayList<String> ids) {

		String json = getJson("", type, "Form", "");
		Gson gson = new Gson();
		FormObject form = gson.fromJson(json, FormObject.class);

		String tableName = form.getTablename();
		String primaryKey = form.getPrimary();

		ArrayList<DataBaseObject> dbos = new ArrayList<DataBaseObject>();

		for (String id : ids) {
			DataBaseObject dbo = new DataBaseObject();
			dbo.setDbName(primaryKey);
			dbo.setNeedsSingleQuotes(true);
			dbo.setFilter(false);
			dbo.setConditions(true);
			dbo.addConditionValue(id);
			dbos.add(dbo);
		}

		ArrayList<ArrayList<DataBaseObject>> data = new ArrayList<ArrayList<DataBaseObject>>();
		data.add(dbos);

		String query = queryBuilder("delete", tableName, data);

//		System.out.println(query);

		if (DB.insertSql(query) != null)
			return true;
		else
			return false;
	}

	public LinkedList<WorkFlowObject> parseWorkFlow(String username,
			String objectName, String type, String name) {
		String json = getJson(username, objectName, type, name);
		Gson gson = new Gson();
		return gson.fromJson(json, new TypeToken<LinkedList<WorkFlowObject>>() {
		}.getType());
	}

	public HashMap<String, String> getKeyValuePairFromTable(String table,
			ArrayList<DataBaseObject> columns) {
		HashMap<String, String> map = new HashMap<String, String>();
		ArrayList<ArrayList<DataBaseObject>> data = new ArrayList<ArrayList<DataBaseObject>>();
		for (DataBaseObject d : columns)
			if (!d.getConditionValues().isEmpty())
				d.setConditions(true);
		data.add(columns);
		String sql = queryBuilder("select", table, data);
		ResultSet rs = DB.execSql(sql);
		try {
			while (rs.next()) {
				map.put(rs.getString(columns.get(0).getDbName()),
						rs.getString(columns.get(1).getDbName()));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return map;
	}

	public TableRows getTableData(String username, String objectName,
			String type, String sName, int page, int pageSize, String order,
			HashMap<String, Filter> filters) {
		TableRows trs = new TableRows();

		String json = getJson(username, objectName, type, sName);
		Gson gson = new Gson();
		ListObject lo = gson.fromJson(json, ListObject.class);

		ArrayList<DataBaseObject> dbos = new ArrayList<DataBaseObject>();
		ArrayList<DataBaseObject> fdbos = new ArrayList<DataBaseObject>();
		int i = 0;
		ArrayList<Integer> hiddenColumns = new ArrayList<Integer>();
		for (ListRow lr : lo.getRows()) {
			DataBaseObject dbo = new DataBaseObject(lr.getDbn(), true);
			if (lr.getDbn().contains("SQL:")) {
				dbo.setDbName(lr.getDbn().replace("SQL:", ""));
				dbo.setValue("noAliasPrefix");
			}
			dbo.setColumnAlias(lr.getHdr());
			String[] fil = new String[0];
			String filField = "";
			if (!lr.getFil().isEmpty()) {
				fil = lr.getFil().split(",");
				filField = lr.getFil();
			}
			if (fil.length == 0)
				dbo.setFilter(false);
			if (lr.getVis() == 0)
				hiddenColumns.add(i);
			i++;
			if (!lr.getFkt().equals("") && !lr.getFkt().contains("FIL:")) {
				String table, alias, id, name;
				String[] fkInfo = lr.getFkt().split("/");
				table = fkInfo[0];
				alias = fkInfo[1];
				id = fkInfo[2];
				name = fkInfo[3];
				Foreign fk = new Foreign(table, alias, name, id, false);
				dbo.foreign.add(fk);
			}
			dbos.add(dbo);

			TableColumn tc = new TableColumn(lr.getHdr(),
					fil.length == 0 ? false : true, lr.getVis() == 1 ? true
							: false, lr.getWid());
			tc.setFilterWidget(fil.length > 0 ? fil[0] : "");
			tc.setFilterOperator(fil.length > 1 ? fil[1] : "");
			tc.setFilterField(filField);
			tc.setForeitn(lr.getFkt());
			trs.addColumn(tc);
		}
		if (!filters.isEmpty()) {
			Set<String> keys = filters.keySet();
			Iterator<String> iterator = keys.iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				Filter f = filters.get(key);
				DataBaseObject fdbo = new DataBaseObject("`" + key + "`", true);
				fdbo.setFilter(true);
				Set<String> k = f.getValue().keySet();
				Iterator<String> it = k.iterator();
				while (it.hasNext())
					fdbo.addConditionValue(f.getValue().get(it.next()));
				fdbo.setCondOperator(f.getOperator());
				fdbo.setDisplay(false);
				dbos.add(fdbo);
				fdbos.add(fdbo);
			}
		}

		trs.setPrimary(lo.getPrimary());
		trs.setReturnColumn(lo.getReturnString());

		ArrayList<ArrayList<DataBaseObject>> data = new ArrayList<ArrayList<DataBaseObject>>();
		data.add(dbos);
		if(order.isEmpty()){
			order = lo.getTablename()+".timestamping DESC";
		}
		String sql = queryBuilder("select", lo.getTablename(), page, pageSize,
				order, data);
		ResultSet rs = DB.execSql(sql);
		String totalSql = queryBuilder("select", lo.getTablename(), 0, 0,
				order, data);
		ResultSet totalRs = DB.execSql(totalSql);
		dbos.removeAll(fdbos);

//		System.out.println(sql);
//		System.out.println(totalSql);

		try {
			trs.setTotal(0);
			if (totalRs == null)
				trs.setTotal(0);
			else if (totalRs.last())
				trs.setTotal(totalRs.getRow());
			if (rs != null) {
				while (rs.next()) {
					TableRow tr = new TableRow();
					int j = 0;
					for (DataBaseObject dbo : dbos) {
						TableData td = new TableData();
						td.setVisible(lo.getRows().get(j).getVis() == 1 ? true
								: false);
						td.setAlignment(lo.getRows().get(j).getAli());
						td.setValue(rs.getString(dbo.getColumnAlias()
								.equals("") ? dbo.getDbName() : dbo
								.getColumnAlias()));
						tr.addTableData(td);
						j++;
					}
					trs.addRow(tr);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		trs.setTableName(lo.getTablename());
		return trs;
	}

	// TODO: MAKE IT READ DB CONNECTION DATA FROM AN XML MAP
	public void selectDataBase(String name) {
		// AppDB.connectionString = "jdbc:postgresql:"+name;
		if (AppDB.getInstance() == null)
			AppDB.getConnection("jdbc:postgresql:" + name);
	}

	protected String queryBuilder(String type, String table, int page,
			int pageSize, String order,
			ArrayList<ArrayList<DataBaseObject>> data) {
		String sql = "";
		if (type.equals("select")) {
			for (ArrayList<DataBaseObject> columns : data)
				sql += selectQueryBuilder(table, page, pageSize, order, columns)
						+ "; ";
		}
		return sql;
	}

	protected String queryBuilder(String type, String table,
			ArrayList<ArrayList<DataBaseObject>> data) {
		String sql = "";

		if (type.equals("select")) {
			for (ArrayList<DataBaseObject> columns : data)
				sql += selectQueryBuilder(table, 0, 0, "", columns) + "; ";
		} else if (type.equals("insert")) {
			for (ArrayList<DataBaseObject> columns : data) {
				sql += "INSERT INTO " + table + " (";
				for (DataBaseObject c : columns) {
					sql += c.getDbName() + ", ";
				}
				sql = sql.substring(0, sql.length() - 2);
				sql += ") VALUES (";
				for (DataBaseObject c : columns) {
					sql += (c.getNeedsSingleQuotes() ? "'" : "") + c.getValue()
							+ (c.getNeedsSingleQuotes() ? "'" : "") + ", ";
				}
				sql = sql.substring(0, sql.length() - 2) + "); ";
			}
		} else if (type.equals("update")) {
			for (ArrayList<DataBaseObject> columns : data) {
				sql += "UPDATE " + table + " SET ";
				for (DataBaseObject c : columns) {
					sql += c.getDbName() + "="
							+ (c.getNeedsSingleQuotes() ? "'" : "")
							+ c.getValue()
							+ (c.getNeedsSingleQuotes() ? "'" : "") + ", ";
				}
				sql = sql.substring(0, sql.length() - 2);
				String conditions = "";
				for (DataBaseObject c : columns) {
					if (c.isConditions()) {
						conditions += c.getDbName() + "="
								+ (c.getNeedsSingleQuotes() ? "'" : "")
								+ c.getConditionValues().get(0)
								+ (c.getNeedsSingleQuotes() ? "'" : "")
								+ " AND ";
					}
				}
				if (!conditions.equals("")) {
					conditions = conditions.substring(0,
							conditions.length() - 5);
					sql += " WHERE " + conditions + "; ";
				}
			}
		} else if (type.equals("delete")) {
			for (ArrayList<DataBaseObject> columns : data) {
				sql += "DELETE FROM " + table;
				String conditions = "";
				for (DataBaseObject c : columns) {
					conditions += c.getDbName() + "="
							+ (c.getNeedsSingleQuotes() ? "'" : "")
							+ c.getConditionValues().get(0)
							+ (c.getNeedsSingleQuotes() ? "'" : "") + " AND ";
				}
				if (!conditions.equals("")) {
					conditions = conditions.substring(0,
							conditions.length() - 5);
					sql += " WHERE " + conditions + "; ";
				}
			}
		}
		return sql;
	}

	protected String selectQueryBuilder(String table, int page, int pageSize,
			String order, ArrayList<DataBaseObject> columns) {
		String tableAlias = "";
		String[] components = table.split(":");
		if (components.length > 1) {
			tableAlias = components[1];
			table = components[0];
		} else
			tableAlias = components[0];
		String sql = "";

		sql = "SELECT ";
		for (DataBaseObject c : columns) {
			if (c.isDisplay()) {
				if (c.foreign != null && !c.foreign.isEmpty()) {
					// Kittens die when this executes ^^ XD
					for (Foreign f : c.foreign) {
						sql += (f.getTableAlias().equals("") ? f.getTableName()
								: f.getTableAlias())
								+ "."
								+ f.getColumnName()
								+ (c.getColumnAlias().equals("") ? ""
										: " AS \"" + c.getColumnAlias() + "\"")
								+ ", ";
					}
				} else {
					sql += (c.getValue().equals("noAliasPrefix") ? ""
							: (tableAlias + "."))
							+ c.getDbName()
							+ (c.getColumnAlias().equals("") ? "" : " AS \""
									+ c.getColumnAlias() + "\"") + ", ";
				}
			}
		}

		sql = sql.substring(0, sql.length() - 2);
		sql += " FROM " + table
				+ (components.length > 1 ? " " + tableAlias : "");

		for (DataBaseObject c : columns) {
			// Kittens die here too :~(
			if (c.foreign != null) {
				for (int i = 0; i < c.foreign.size(); i++) {
					sql += " INNER JOIN "
							+ c.foreign.get(i).getTableName()
							+ " "
							+ c.foreign.get(i).getTableAlias()
							+ " ON "
							+ (i == 0 ? tableAlias + "." + c.getDbName()
									: (c.foreign.get(i).getTableAlias()
											.equals("") ? c.foreign.get(i - 1)
											.getTableName() : c.foreign.get(i)
											.getTableAlias())
											+ "."
											+ c.foreign.get(i - 1)
													.getIdColumnName())
							+ " = "
							+ (c.foreign.get(i).getTableAlias().equals("") ? c.foreign
									.get(i).getTableName() : c.foreign.get(i)
									.getTableAlias()) + "."
							+ c.foreign.get(i).getIdColumnName() + " ";
				}
			}
		}

		String conditions = "";
		String filters = "(";
		boolean hasFilters = false, cond = false;

		for (DataBaseObject c : columns) {
			String comparator = "";
			if (c.getConditionValues().size() > 1) {
				if (c.getCondOperator().equals("IN")
						|| c.getCondOperator().equals("NOT IN")) {
					comparator = c.getCondOperator().equals("IN") ? " IN ("
							: " NOT IN (";
					for (String s : c.getConditionValues())
						comparator += "'" + s + "',";
					comparator += comparator.substring(0,
							comparator.length() - 1) + ")";
				} else if (c.getCondOperator().equals("BETWEEN")) {
					if (c.getConditionValues().size() == 2)
						comparator = " BETWEEN '"
								+ c.getConditionValues().get(1) + "' AND '"
								+ c.getConditionValues().get(0) + "'";
				}
			} else if (c.getConditionValues().size() > 0) {
				if (c.getCondOperator().equals("LIKE")) {
					comparator = " LIKE '" + c.getConditionValues().get(0)
							+ "%'";
				} else {
					comparator = " " + c.getCondOperator() + " '"
							+ c.getConditionValues().get(0) + "'";
				}
			}

			if (c.isConditions() && !c.isFilter() && !comparator.isEmpty()) {
				conditions += tableAlias + "." + c.getDbName() + comparator
						+ " AND ";
				cond = true;
			}
			if (c.isFilter()) {
				if (!comparator.isEmpty()) {
					filters += c.getDbName() + comparator + " AND ";
					hasFilters = true;
				}
			}
		}

		if (cond) {
			conditions = conditions.substring(0, conditions.length() - 5);
			sql += " WHERE " + conditions;
		}

		if (hasFilters) {
			sql += " HAVING " + filters.substring(0, filters.length() - 4)
					+ ")";
		}

		if (!order.equals("")) {
			sql += " ORDER BY " + order;
		}

		if (pageSize != 0 && page != 0) {
			String limit = pageSize + "";
			String offset = ((page - 1) * pageSize) + "";
			sql += " LIMIT " + limit + " OFFSET " + offset;
		}

		return sql;
	}

	public String validateListView(HashMap<String, String> params, String value) {
		String listID = params.get("listID");
		String listName = params.get("listName");
		
		String json = getJson(params.get("username"), params.get("objectName"), "List", params.get("name"));
		Gson gson = new Gson();
		
		ListObject listObject = gson.fromJson(json, ListObject.class);
		
//		Integer idLocation = listObject.getPrimary();
		
		String dbName = "";
		String dbValue = "";
		String id = "";
		String retId = "";
		String tableName = listObject.getTablename();
		
		for(ListRow listRow:listObject.getRows()){			
			if(listRow.getHdr().equals(listName)){
				dbName = listRow.getDbn();
				dbValue = value;
			}
			if(listRow.getHdr().equals(listID)){
				id = listRow.getDbn();
			}
		}
		
		DataBaseObject condition = new DataBaseObject();
		condition.setDbName(dbName);
		condition.setConditions(true);
		condition.setFilter(false);
		condition.addConditionValue(dbValue);
		
		DataBaseObject _id = new DataBaseObject();
		_id.setDbName(id);
		
		ArrayList<DataBaseObject> dbo = new ArrayList<DataBaseObject>();
		dbo.add(_id);
		dbo.add(condition);
		
		ArrayList<ArrayList<DataBaseObject>> data = new ArrayList<ArrayList<DataBaseObject>>();
		data.add(dbo);
		
		String query = queryBuilder("select", tableName, data);
		
		ResultSet rs = DB.execSql(query);
		try {
			if(rs.next()){
				if(rs.getString(id) != null){
					retId = rs.getString(id);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return retId;
		
	}

	public String generateReport(String username, String objectName, String objectType, String name, String order, HashMap<String, Filter> filters, HashMap<String, Boolean> visible, int pageType){
		TableRows tableRows = getTableData(username, objectName, "List","Report", 0, 99999999, order, filters);
		return ReportGenerator.getInstance().generateReport(objectName, tableRows, visible, pageType);
	}
	
}